﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Bing.Toolkit.Data.SQL
{
    /// <summary>
    /// SQLServer数据库操作对像
    /// </summary>
    public class SQLServerDAO : DataAccessObject
    {
        public const string SQL_PAGING_COUNT = "SELECT COUNT(*) FROM ({0}) AS T";
        public const string SQL_PAGING = @"SELECT * FROM 
(
	SELECT *,ROW_NUMBER() OVER(ORDER BY {0}) AS I FROM 
	(
		{1} 
	) AS T
) AS T
WHERE T.I > {2} AND T.I <= {3}";
        // <summary>
        /// 获取最后Insert数据ID的SQL语句
        /// </summary>
        private const string SQL_SELECT_IDENTITY = "SELECT @@IDENTITY;";
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connection"></param>
        public SQLServerDAO(DbConnection connection) : base(connection) { }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString"></param>
        public SQLServerDAO(string connectionString) : base(connectionString, "System.Data.SqlClient") { }
        /// <summary>
        /// 返回最后一次更新的自增ID
        /// </summary>
        /// <returns></returns>
        public int GetLastInsertId()
        {
            return ScalarValue<int>(SQL_SELECT_IDENTITY);
        }
        /// <summary>
        /// 批量更新
        /// </summary>
        /// <param name="dtbl">DataTable对像</param>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString"></param>
        public void SqlBluck(DataTable dtbl, string tableName)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy((SqlConnection)Connection))
            {
                sbc.DestinationTableName = tableName;
                sbc.WriteToServer(dtbl);
            }
        }
        /// <summary>
        /// 执行存储过程并反回结果集
        /// </summary>
        /// <param name="sqlText">数据源运行的文本命令</param>
        /// <param name="cmdParams">查询参数集合</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteStoredProcedureDataSet(string commandText, params DbParameter[] args)
        {
            using (DbCommand command = PrepareCommand(commandText))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(args);
                DbDataAdapter adaper = new SqlDataAdapter();
                adaper.SelectCommand = command;
                DataSet result = new DataSet();
                adaper.Fill(result);
                return result;
            }
        }
        /// <summary>
        /// 执行存储过程并反回一个值
        /// </summary>
        /// <typeparam name="T">值的类型,可以是int,string,float,double...</typeparam>
        /// <param name="commandText">数据源运行的文本命令</param>
        /// <param name="args">参数对象</param>
        /// <returns></returns>
        public T ExecuteStoredProcedureScalar<T>(string commandText, params DbParameter[] args)
        {
            using (DbCommand command = PrepareCommand(commandText))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(args);
                EnsureConnectionOpened();
                var result = command.ExecuteScalar();
                return (T)result;
            }
        }
        /// <summary>
        /// 执行存储过程并反回一个DataReader对象
        /// </summary>
        /// <param name="commandText">数据源运行的文本命令</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public DbDataReader ExecuteStoredProcedureReader(string commandText, params DbParameter[] args)
        {
            using (DbCommand command = PrepareCommand(commandText))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(args);
                EnsureConnectionOpened();
                return command.ExecuteReader();
            }
        }
        /// <summary>
        /// 执行存储过程并反回一个KeyValueObject对象集合
        /// </summary>
        /// <param name="commandText">数据源运行的文本命令</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public List<KeyValueObject> ExecuteStoredProcedureKeyValueObjectList(string commandText, params DbParameter[] args)
        {
            using (IDataReader dataReader = ExecuteStoredProcedureReader(commandText, args))
            {
                return KeyValueObject.KeyValueObjectListWithDataReader(dataReader);
            }
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页的数量</param>
        /// <param name="args">参数</param>
        /// <returns></returns>
        public override PageOfList<KeyValueObject> Paging(string commandText, string orderField, int pageIndex, int pageSize, params object[] args)
        {
            var count = ScalarValue<int>(String.Format(SQL_PAGING_COUNT, commandText), args);
            var sql = String.Format(SQL_PAGING, orderField, commandText, (pageIndex - 1) * pageSize, pageSize * pageIndex);
            var dataItem = Query(sql, args);
            return new PageOfList<KeyValueObject>(dataItem, pageIndex, pageSize, count);
        }

        public override DbParameter CreateParameter(string key, object val)
        {
            return new SqlParameter(key, val);
        }
    }
}
